﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;

namespace StockConsoleApplication
{
    class AccessOpt
    {
        private static string dbName = "stock.mdb";

        private static OleDbConnection oleCon;

        //连接Access数据库
        public static void connectAccess()
        {
            //Access连接信息
            string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName + ";";
            oleCon = new OleDbConnection(conStr);
            //建立数据库引擎连接，注意数据表应放在DEBUG文件下
            oleCon.Open();
        }

        public static void closeConnection()
        {
            if(oleCon !=null)
            {
                oleCon.Close();
            }
        }

        //初始化数据库环境
        public static void init()
        {
            //清除缓存表
            string clear1 = "delete from tmp_before";
            string clear2 = "delete from tmp_after";
            string clear3 = "delete from stock_output";
            new OleDbCommand(clear1, oleCon).ExecuteNonQuery();
            new OleDbCommand(clear2, oleCon).ExecuteNonQuery();
            new OleDbCommand(clear3, oleCon).ExecuteNonQuery();
            Console.WriteLine("初始化数据库环境执行完成#####");
        }

        //根据给定月份计算过去6个月的个股回报率
        public static int past(Stock stock)
        {
            string clear1 = "delete from tmp_before";
            new OleDbCommand(clear1, oleCon).ExecuteNonQuery();
            string sql = "INSERT INTO tmp_before SELECT oa.Stkcd as Stkcd, oa.Trdmnt as Trdmnt, (ob.Mclsprc - oc.Mclsprc) / oc.Mclsprc as Rate FROM "
                    + "(SELECT a.Stkcd as Stkcd, a.Mclsprc as Mclsprc, a.Trdmnt as Trdmnt FROM stock a where a.Trdmnt = '"+ stock.Chsmnt + "') as oa,"
                    + "(SELECT b.Stkcd as Stkcd, b.Mclsprc as Mclsprc, b.Trdmnt as Trdmnt FROM stock b where b.Trdmnt = '"+ stock.Beforemnt1 +"') as ob, "
                    + "(SELECT c.Stkcd as Stkcd, c.Mclsprc as Mclsprc, c.Trdmnt as Trdmnt FROM stock c where c.Trdmnt = '"+ stock.Beforemnt6 +"') as oc "
                    + "WHERE oa.Stkcd = ob.Stkcd AND oa.Stkcd = oc.Stkcd";
            Console.Write("正在执行脚本：");
            int all = new OleDbCommand(sql, oleCon).ExecuteNonQuery();
            stock.Chsnum = all / 10;
            return all;
        }

        //选择赢家组合
        public static int winAfter(Stock stock)
        {
            string clear2 = "delete from tmp_after";
            new OleDbCommand(clear2, oleCon).ExecuteNonQuery();
            string sql = "INSERT INTO tmp_after SELECT oa.Stkcd as Stkcd, oa.Trdmnt as Trdmnt, (ob.Mclsprc-oc.Mclsprc)/oc.Mclsprc as Rate FROM "
                    + "(SELECT TOP " + stock.Chsnum + " t.Stkcd as Stkcd, t.Trdmnt as Trdmnt FROM tmp_before t ORDER BY Rate DESC) oa, "
                    + "(SELECT b.Stkcd as Stkcd, b.Mclsprc as Mclsprc, b.Trdmnt as Trdmnt FROM stock b where b.Trdmnt = '" + stock.Aftermnt6 + "') as ob, "
                    + "(SELECT c.Stkcd as Stkcd, c.Mclsprc as Mclsprc, c.Trdmnt as Trdmnt FROM stock c where c.Trdmnt = '" + stock.Beforemnt1 + "') as oc "
                    + "WHERE oa.Stkcd = ob.Stkcd AND oa.Stkcd = oc.Stkcd";
            //Console.WriteLine("执行脚本：" + sql);
            Console.Write("...");
            return new OleDbCommand(sql, oleCon).ExecuteNonQuery();
        }

        //选择输家组合
        public static int loseAfter(Stock stock)
        {
            string clear2 = "delete from tmp_after";
            new OleDbCommand(clear2, oleCon).ExecuteNonQuery();
            string sql = "INSERT INTO tmp_after SELECT oa.Stkcd as Stkcd, oa.Trdmnt as Trdmnt, (ob.Mclsprc-oc.Mclsprc)/oc.Mclsprc as Rate FROM "
                    + "(SELECT TOP " + stock.Chsnum + " t.Stkcd as Stkcd, t.Trdmnt as Trdmnt FROM tmp_before t ORDER BY Rate ASC) oa,"
                    + "(SELECT b.Stkcd as Stkcd, b.Mclsprc as Mclsprc, b.Trdmnt as Trdmnt FROM stock b where b.Trdmnt = '" + stock.Aftermnt6 + "') as ob,"
                    + "(SELECT c.Stkcd as Stkcd, c.Mclsprc as Mclsprc, c.Trdmnt as Trdmnt FROM stock c where c.Trdmnt = '" + stock.Beforemnt1 + "') as oc "
                    + "WHERE oa.Stkcd = ob.Stkcd AND oa.Stkcd = oc.Stkcd";
            // Console.WriteLine("执行脚本：" + sql);
            Console.Write("...");
            return new OleDbCommand(sql, oleCon).ExecuteNonQuery();
        }

        //得到结果
        public static decimal getResult()
        {
            string sql = "SELECT sum(t.Rate)/count(t.Stkcd) as result FROM tmp_after t";
            //Console.WriteLine("执行脚本：" + sql);
            Console.Write("...");
            OleDbDataReader reader = new OleDbCommand(sql, oleCon).ExecuteReader();
            decimal result = 0;
            try
            {
                result = reader.Read() ? new Decimal(reader.GetDouble(0)) : 0;
            }
            catch (InvalidCastException)
            {
                Console.WriteLine();
                Console.WriteLine("原始数据不全，产生异常！此选股无效");
            }
            return result;
        }

        //写入结果
        public static int insertResult(Stock stock)
        {
            string sql = "INSERT INTO stock_output VALUES ( '"+ stock.Chsmnt+"',"+ stock.Winrt +", " + stock.Losert + ")";
            //Console.WriteLine("执行脚本：" + sql);
            Console.Write("...");
            Console.WriteLine();
            return new OleDbCommand(sql, oleCon).ExecuteNonQuery();
        }
    }
}
